import requests
from openpyxl import *
from time import *

def write(list):
    wb = Workbook()
    ws = wb.active
    for r in range(len(list)):
        for c in range(len(list[0])):
            ws.cell(r+1, c+1).value = list[r][c]
    wb.save('power_file'+'.xlsx')
    print('writting finished')

def reading():
    wb = load_workbook('power_file.xlsx')
    sheet_list = wb.sheetnames  # 返回一个列表
    ws = wb[sheet_list[0]]  # index为0为第一张表
    last_one = []
    for r in range(1, ws.max_row+1):
        row_list = []  # 每一行建立一个list
        for c in range(1, ws.max_column+1):
            v = ws.cell(r, c).value
            row_list.append(v)
        last_one.append(row_list)
    print(last_one[0])
    wb.close()
    return last_one

def test_program(last_one, recent):
    list = []
    t_start=time()
    for page in range(100):
        url = 'https://api.data.um.edu.mo/service/facilities/power_consumption/v1.0.0/E11/records?&page='+str(
            recent*100+page+1)
        response = requests.get(url, headers={"Authorization":"Bearer a6991e70-14d9-3fb2-a97c-ed91fdc0fc0c"}, timeout=50)
        data = response.json()
        for data in data['_embedded']:
            meterCode = data['meterCode']
            if meterCode == '2A06_1':
                recordDatetime = data['recordDatetime']
                readings = data['readings']['kwh']
                zoneCode = data['zoneCode']
                #print([meterCode,recordDatetime,readings,zoneCode])
                list.append([meterCode, recordDatetime, readings, zoneCode])
                if list[-1] == last_one[0]:
                    list.pop()
                    for i in range(len(last_one)):
                        list.append(last_one[i])
                    return list, 0
        if (recent*100+page+1)%10 == 0:
            print(recent*100+page+1,end=' ')
    print('')
    print(time()-t_start)
    return list,(recent+1)

if __name__ == '__main__':
    last_one = reading()
    total_list = []
    recent = 20
    for i in range(100):
        list,recent = test_program(last_one, recent)
        print(list[0][1])
        print(list[-1][1])
        total_list.append(list)
    write(total_list)
